package gameSite;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Vector;

public class ProductTableGateway {

	private Connection db;
	
	private static String queryGamesByConsole = "SELECT * FROM Products WHERE console = ?";
	private static String queryGamesByCategory = "SELECT * FROM Products WHERE TypeOfGame = ?";
	private static String queryGamesByPriceAndConsole = "SELECT * FROM Products WHERE console = ? ORDER BY price";
	private static String queryGamesAll = "SELECT * FROM Products";
	private static String queryGamesAllByConsole = "SELECT * FROM Products ORDER BY console";
	private static String queryGamesAllByPrice = "SELECT * FROM Products ORDER BY price";
	private static String queryGamesAllByCategory = "SELECT * FROM Products ORDER BY category";
	private static String queryGamesByID = "SELECT * FROM Products WHERE productID = ?";
	private static String queryInsertNewGame = "INSERT INTO Products VALUES(?,?,?,?,?,?,?)";
	
	
	public void addNewGameToDatabase(int id, String title, String console, String category, String details, int price, String imageName) throws ClassNotFoundException, SQLException
	{
		JDBCdriver jdbc = new JDBCdriver();
		if (db == null)
		{
			db =jdbc.getConnectionToDatabase();
		}
		java.sql.PreparedStatement stmt = db.prepareStatement(queryInsertNewGame);
		stmt.setInt(1, id);
		stmt.setString(2, title);
		stmt.setString(3, console);
		stmt.setString(4, category);
		stmt.setString(5, details);
		stmt.setInt(6, price);
		stmt.setString(7, imageName);
		stmt.executeQuery();
	}
	
	public RecordSet getGamesByID (int id) throws ClassNotFoundException, SQLException
	{
		JDBCdriver jdbc = new JDBCdriver();
		if (db == null)
		{
			db =jdbc.getConnectionToDatabase();
		}
		java.sql.PreparedStatement stmt = db.prepareStatement(queryGamesByID);
		stmt.setInt(1, id);
		ResultSet rs = stmt.executeQuery();
		return resultSetToRecordSet(rs);
	}
	
	public RecordSet getGamesByConsole(String console) throws ClassNotFoundException, SQLException
	{
		JDBCdriver jdbc = new JDBCdriver();
		if (db == null)
		{
			db =jdbc.getConnectionToDatabase();
		}
		java.sql.PreparedStatement stmt = db.prepareStatement(queryGamesByConsole);
		stmt.setString(1, console);
		ResultSet rs = stmt.executeQuery();
		return resultSetToRecordSet(rs);
	}

	
	
	

	public RecordSet getGamesByCategory(String category)throws ClassNotFoundException, SQLException
	{
		JDBCdriver jdbc = new JDBCdriver();
		if (db == null)
		{
			db =jdbc.getConnectionToDatabase();
		}
		
		if(db != null)
		{
		java.sql.PreparedStatement stmt = db.prepareStatement(queryGamesByCategory);
		stmt.setString(1, category);
		ResultSet rs = stmt.executeQuery();
		return resultSetToRecordSet(rs);
		}
		else
		{
			System.out.println("database connection is null");
			return null;
		}
	}
	
	
	
	
	public RecordSet getGamesByPriceAndConsole(String console)throws ClassNotFoundException, SQLException
	{
		JDBCdriver jdbc = new JDBCdriver();
		if (db == null)
		{
			db =jdbc.getConnectionToDatabase();
		}
		java.sql.PreparedStatement stmt = db.prepareStatement(queryGamesByPriceAndConsole);
		stmt.setString(1, console);
		ResultSet rs = stmt.executeQuery();
		return resultSetToRecordSet(rs);
	}
	
	
	
	
	
	public RecordSet getGamesAll()throws ClassNotFoundException, SQLException
	{
		JDBCdriver jdbc = new JDBCdriver();
		if (db == null)
		{
			db =jdbc.getConnectionToDatabase();
		}
		java.sql.PreparedStatement stmt = db.prepareStatement(queryGamesAll);
		ResultSet rs = stmt.executeQuery();
		return resultSetToRecordSet(rs);
	}
	
	
	
	
	
	public RecordSet getGamesAllByConsole()throws ClassNotFoundException, SQLException
	{
		JDBCdriver jdbc = new JDBCdriver();
		if (db == null)
		{
			db =jdbc.getConnectionToDatabase();
		}
		java.sql.PreparedStatement stmt = db.prepareStatement(queryGamesAllByConsole);
		ResultSet rs = stmt.executeQuery();
		return resultSetToRecordSet(rs);
	}
	
	
	
	
	
	public RecordSet getGamesAllByCategory()throws ClassNotFoundException, SQLException
	{
		JDBCdriver jdbc = new JDBCdriver();
		if (db == null)
		{
			db =jdbc.getConnectionToDatabase();
		}
		java.sql.PreparedStatement stmt = db.prepareStatement(queryGamesAllByCategory);
		ResultSet rs = stmt.executeQuery();
		return resultSetToRecordSet(rs);
	}
	
	
	
	
	
	public RecordSet getGamesAllByPrice(String console)throws ClassNotFoundException, SQLException
	{
		JDBCdriver jdbc = new JDBCdriver();
		if (db == null)
		{
			db =jdbc.getConnectionToDatabase();
		}
		java.sql.PreparedStatement stmt = db.prepareStatement(queryGamesAllByPrice);
		ResultSet rs = stmt.executeQuery();
		return resultSetToRecordSet(rs);
	}
	
	
	
	
 	private RecordSet resultSetToRecordSet(ResultSet rs) throws SQLException 
 	{
		ResultSetMetaData metaData = rs.getMetaData();

		// names of columns
		Vector<String> columnNames = new Vector<String>();
		int columnCount = metaData.getColumnCount();
		for (int column = 1; column <= columnCount; column++) {
			columnNames.add(metaData.getColumnName(column));
		}

		// data of the table
		Vector<Vector<Object>> data = new Vector<Vector<Object>>();
		while (rs.next()) {
			Vector<Object> vector = new Vector<Object>();
			for (int columnIndex = 1; columnIndex <= columnCount; columnIndex++) {
				vector.add(rs.getObject(columnIndex));
			}
			data.add(vector);
		}

		return new RecordSet(data, columnNames);

	}

	
}
